import xlrd,os,requests,datetime
from django.shortcuts import render
from django.http import HttpResponse
from django.db import connection
from django.db.models import Q
from . import models
# Create your views here.

# 功能列表
def Hello_price(request):
    # return HttpResponse('hello,price')
    return render(request,'price_analysis/index.html',{})

# 将数据库股票列表清零后，写入'股票列表.xls'中的数据
def get_stocks_list(request):
    #获取路径
    stocks_list_path=os.path.abspath('../股票列表.xls')
    stocks_list_data=xlrd.open_workbook(stocks_list_path)
    stocks_list_table=stocks_list_data.sheet_by_name('Sheet1') # excel中列表
    stock_list_database =models.stocks_list.objects.all() #数据库中列表
    stock_list_ins=[]
    for i in range(1,stocks_list_table.nrows):
        code = '{:0>6d}'.format(int(stocks_list_table.row_values(i)[1]))
        name = stocks_list_table.row_values(i)[2]
        if len(stock_list_database) != 0:
            # stock = models.stocks_list(stock_code=code, stock_name=name)
            # stock_list_ins.append(stock)
            # stock.save()
            models.stocks_list.objects.all().delete()
            with connection.cursor() as cursor:
                cursor.execute('update sqlite_sequence set seq=0 where name="price_analysis_stocks_list"')
            # for row in stock_list_database:
            #     if str(row.stock_code) == str(code):
            #         # print(row.stock_code, code)
            #         continue
        stock = models.stocks_list(stock_code=code, stock_name=name)
        stock_list_ins.append(stock)
    try:
        models.stocks_list.objects.bulk_create(stock_list_ins)
    except Exception as e:
        print('有重复项，请筛选数据表')
        return HttpResponse('有重复项，请筛选数据表')
    stock_list=models.stocks_list.objects.all()
    # 判断excel中数据是否是上证股票
    stock_name_list=[]
    # end_time = datetime.datetime.now()
    # begin_time = end_time - datetime.timedelta(days=15)
    # for stock in stock_list:
    #     html=get_date(stock.stock_code,begin_time.strftime('%Y-%m-%d'),end_time.strftime('%Y-%m-%d'))
    #     for row in html.text.split('\n')[1:]:
    #         row=row.split(',')
    #         if row==[''] or row[0]=='日期':
    #             continue
    #         print(row[0],row[1])



    return render(request,'price_analysis/get_stock_list.html',{
        'stock_list':stock_list
        # ,'stock_name_list':stock
    })
# 封装：获取股票交易数据的request请求：
def get_date(stock_code,begin_time,now_time):
    # 60是沪股，00是深股
    if str(stock_code) == '000001' or stock_code.startswith('60'):
        url = 'http://quotes.money.163.com/service/chddata.html?code=0{}&start={}&end={}' \
              '&fields=TCLOSE;HIGH;LOW;TOPEN;LCLOSE;CHG;PCHG;TURNOVER;VOTURNOVER;VATURNOVER;TCAP;MCAP' \
              ''.format(stock_code, begin_time,now_time)
    elif stock_code.startswith('00') or stock_code.startswith('30'):
        url = 'http://quotes.money.163.com/service/chddata.html?code=1{}&start={}&end={}' \
              '&fields=TCLOSE;HIGH;LOW;TOPEN;LCLOSE;CHG;PCHG;TURNOVER;VOTURNOVER;VATURNOVER;TCAP;MCAP' \
              ''.format(stock_code, begin_time,now_time)
    else:
        print('url错误')
    html = requests.get(url)
    html.encoding = 'gbk'
    return html
# 获取股票交易数据
def get_stock_trans(request):
    # 获取股票列表
    stocks_list=models.stocks_list.objects.all()
    # 清空日交易表中数据，并将id自增重置为1
    models.daily_trans.objects.all().delete()
    with connection.cursor() as cursor:
        cursor.execute('update sqlite_sequence set seq=0 where name="price_analysis_daily_trans"')
    for stock in stocks_list:
        stock_code=stock.stock_code
        # print(stock_code,stock_code.startswith('00'))

        # 获取交易数据
        html=get_date(stock_code,'20190701','20200101')
        daily_trans_list = []
        # 剔除第一行数据，然后插入交易数据
        for row in html.text.split('\n')[1:]:
            row=row.split(',')
            if row==[''] or row[0]=='日期':
                continue
            # stock_code=str(row[1][1:])
            stock = models.stocks_list.objects.get(stock_code=stock_code)
            daily_data=models.daily_trans(riQi=row[0],guPiaoCode=stock,guPiaoName=row[2],shouPan=row[3],zuiGao=row[4],
                               zuiDi=row[5],kaiPan=row[6],qianShouPan=row[7],zhangDieE=row[8],zhangDieFu=row[9],
                               huanShouLv=row[10],chenJiaoLiang=row[11],chengJiaoJinE=row[12],
                               zongShiZhi=row[13],liuTongShiZhi=row[14])
            daily_trans_list.append(daily_data)
        # print(daily_trans_list)
        models.daily_trans.objects.bulk_create(daily_trans_list)


    # print(stock.stock_name)
    # stock_trans_list=html.text.replace(',',' | ').split('\n')
    time=datetime.datetime(2019,12,31,0,0)
    stock_trans_list=models.daily_trans.objects.filter(riQi=time)
    for stock_trans in stock_trans_list:
        stock_trans.riQi=stock_trans.riQi.strftime('%Y-%m-%d')
    return render(request,'price_analysis/get_stock_trans.html',{
        'stock_trans_list':stock_trans_list
    })
# 获得指定天数的最低价最低价
def get_zuidi(request,days):
    #查询股票列表
    stock_list=models.stocks_list.objects.all()
    #设置开始和结束时间
    end_time=datetime.datetime.now()
    begin_time=end_time-datetime.timedelta(days=days)
    #将每个股票最后一个交易日数据和指定时间段最低价比较
    stock_trans_list=[] #最后一个交易日股票models列表
    zuidi_list=[] #近期股票最低价数据的models列表
    cal_datas=[]#需计算的数据

    for stock in stock_list:
        stock_code=str(stock.stock_code)
        # 获取最近一个交易日的数据和最近时间段的最低价数据
        # print(a[0].guPiaoName)
        stock_trans_data=models.daily_trans.objects.filter(guPiaoCode=stock_code).order_by('-riQi')
        if not stock_trans_data.exists():
            print(stock.stock_name,'没有数据')
            continue
        trans_lastday = stock_trans_data[0] #上一个交易日
        trans_near_days=models.daily_trans.objects.filter(Q(guPiaoCode=stock_code) & Q(riQi__gte=begin_time) & Q(riQi__lte=end_time)).extra(select={'floatzuiDi':'zuiDi+0.1'}).order_by('floatzuiDi')[0]
        # print(trans_lastday.shouPan,trans_near_days.zuiDi)
        #将最低价数据和上一日数据加入列表
        if float(trans_lastday.shouPan)<float(trans_near_days.zuiDi)*1.05:
            trans_lastday.riQi=trans_lastday.riQi.strftime('%Y-%m-%d')
            trans_near_days.riQi=trans_near_days.riQi.strftime('%Y-%m-%d')
            stock_trans_list.append(trans_lastday)
            zuidi_list.append(trans_near_days)
            # 新建列表，用于存放计算数据
            up_percent=(float(trans_lastday.shouPan)/float(trans_near_days.zuiDi))*100-100
            up_percent='%.2f%%' % up_percent
            cal_data = models.cal_data()
            cal_data.guPiaoCode=trans_lastday.guPiaoCode
            cal_data.up_percent=up_percent
            cal_datas.append(cal_data)

        # print(zuidi_list[0])

    # stock_trans_list=models.daily_trans.objects.filter(Q(riQi__gte=begin_time) & Q(riQi__lte=end_time))
    # index置为0和1，以改变奇偶行颜色
    for index,stock_trans in enumerate(stock_trans_list):
        index=index%2
        stock_trans.id=index
    # for i in cal_datas:
    #     print(i.guPiaoCode,' ', i.up_percent)
    return render(request,'price_analysis/get_zuidi.html',{
        'stock_trans_list': stock_trans_list,
        'zuidi_list':zuidi_list,
        'days':days,
        'cal_datas':cal_datas
    })

def update_stock_trans(request):
    # 查询股票列表
    stock_list = models.stocks_list.objects.all()
    now = datetime.datetime.now().strftime('%Y%m%d')
    response_text_1=''
    # 获取每只股票的最新日期,在获取+1后到今天的股票数据
    for stock in stock_list:
        stock_code=str(stock.stock_code)
        stock_trans_data=models.daily_trans.objects.filter(guPiaoCode=stock_code).order_by('-riQi')
        if not stock_trans_data.exists():
            print(stock.stock_name, '没有数据')
            continue
        trans_last_data=stock_trans_data[0]
        days=datetime.timedelta(days=1)
        begin_time = (trans_last_data.riQi + days).strftime('%Y%m%d')
        now_time = datetime.datetime.now().strftime('%Y%m%d')
        # 已有数据最后一条时间大于等于今天，不更新，否则更新
        if now_time<=begin_time:
            # print('数据已经是最新')
            response_text_1='数据已经是最新'
            continue
        stock_code = stock.stock_code
        # 获取交易数据
        html=get_date(stock_code,begin_time,now_time)
        # print(html.text)
        daily_trans_list = []
        # 剔除第一行数据，然后插入交易数据
        for row in html.text.split('\n')[1:]:
            row = row.split(',')
            if row == [''] or row[0] == '日期':
                continue
            # stock_code=str(row[1][1:])
            stock = models.stocks_list.objects.get(stock_code=stock_code)
            daily_data = models.daily_trans(riQi=row[0], guPiaoCode=stock, guPiaoName=row[2], shouPan=row[3],
                                            zuiGao=row[4],
                                            zuiDi=row[5], kaiPan=row[6], qianShouPan=row[7], zhangDieE=row[8],
                                            zhangDieFu=row[9],
                                            huanShouLv=row[10], chenJiaoLiang=row[11], chengJiaoJinE=row[12],
                                            zongShiZhi=row[13], liuTongShiZhi=row[14])
            daily_trans_list.append(daily_data)
        # print(daily_trans_list)
        models.daily_trans.objects.bulk_create(daily_trans_list)
    stock_000001=models.daily_trans.objects.filter(guPiaoCode='000001').order_by('-riQi')[0]
    last_day=stock_000001.riQi.strftime('%Y-%m-%d')
    response_text_2='数据更新至'+last_day
    return HttpResponse(response_text_1+'</br>'+response_text_2)




